package model.dao;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.SQLAdapter;

public class AplicativoDAO{
	private static final String SQL_SELECT_DISTINCT_NOME = "SELECT DISTINCT a.nome FROM app a ORDER BY a.nome ASC";
	private static final String SQL_SELECT_DATA_PRIMEIRO_REGISTRO = "SELECT a.periodo FROM app a ORDER BY a.periodo ASC LIMIT 1";
	private static final String SQL_SELECT_QUANTIDADE_ACESSO = "SELECT COUNT(a.nome) AS count, a.nome FROM app a WHERE a.estado = 'start' AND a.nome = ? AND a.periodo >= ? AND a.periodo <= ?";	
	private static final String SQL_SELECT_TEMPO_MEDIO = "SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(a.duracao))/COUNT(a.duracao)), '%H:%i:%s') AS media FROM app a WHERE a.nome = ? AND a.estado = 'finish' AND a.duracao > 0 AND a.periodo >= ? AND a.periodo <= ?";
	private static SQLAdapter sqlAdapter;
	
    private static SQLAdapter getSqlAdapter(){
    	if(sqlAdapter == null){
    		sqlAdapter = new SQLAdapter(new File("bd/bd_config.xml"));
    	}
		return sqlAdapter;
	}
    
	public static void setSqlAdapter(String driver, String protocol, String host, String database, String username, String password){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(driver, protocol, host, database, username, password);			
		}else{
			getSqlAdapter().getDataSource().changeConnection(driver, protocol, host, database, username, password);
		}
	}
	
	public static void setSqlAdapter(File configFile){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(configFile);			
		}else{
			getSqlAdapter().getDataSource().changeConnection(configFile);
		}
	}
	
	public static List<String> queryDistinctNomes() throws SQLException, NullPointerException{
    	if(getSqlAdapter() != null){
    		List<String> aplicacoes = new ArrayList<String>();
    		ResultSet resultSet = getSqlAdapter().executeQueryStatement(SQL_SELECT_DISTINCT_NOME);
    		while(resultSet.next()){
    			aplicacoes.add(resultSet.getString("a.nome"));
    		}
    		getSqlAdapter().close();
            
            return aplicacoes;
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
	
	public static Date queryDataPrimeiroRegistro() throws SQLException, NullPointerException{
		if(getSqlAdapter() != null){
			Date date = null;
			
			ResultSet resultSet = getSqlAdapter().executeQueryStatement(SQL_SELECT_DATA_PRIMEIRO_REGISTRO);
			while(resultSet.next()){
				date = resultSet.getDate("a.periodo");
			}
			getSqlAdapter().close();
			
			return date;
		}else{
			throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
		}
	}
	
	public static int queryQuatidadeAcesso(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
    	if(getSqlAdapter() != null){
    		PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_QUANTIDADE_ACESSO);
            if(ps != null){
            	int count = 0;
            	
            	ps.setString(1, aplicacaoNome);
            	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
        		ResultSet resultSet = ps.executeQuery();
        		while(resultSet.next()){
        			count = resultSet.getInt("count");
        		}
        		getSqlAdapter().close();
                
                return count;
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
    
	public static Time queryTempoMedio(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
		if(getSqlAdapter() != null){
			PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_TEMPO_MEDIO);
            if(ps != null){
            	Time time = null;
    			
               	ps.setString(1, aplicacaoNome);
               	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
               	ResultSet resultSet = ps.executeQuery();
        		while(resultSet.next()){
        			time = resultSet.getTime("media");
        		}
        		getSqlAdapter().close();
                
        		return time;
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
	
    public static void main(String[] args){
    	try{
			List<String> aplicacoes = queryDistinctNomes();
			for(int i=0; i<aplicacoes.size(); i++){
				System.out.println(aplicacoes.get(i));
				System.out.println(queryQuatidadeAcesso(aplicacoes.get(i), "2012-8-7", "2012-11-1"));				
				System.out.println(queryTempoMedio(aplicacoes.get(i), "2012-8-7", "2012-11-1"));
				System.out.println();
			}
			
		}catch(NullPointerException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}